﻿using System;
using System.Data;
using TopBuilds.Database;

namespace TopBuilds.Restaurant.Data
{
    public class MemberObject
    {
        public static Member[] GetMembers()
        {
            return Query(null);
        }

        public static Member GetMember(int id)
        {
            Member member = new Member();

            SqlBuilder builder = new SqlBuilder();
            builder.ConditionAdd(MemberDesc.Id, ConditionExpress.EQUALS, id);
            builder.QueryTableName = MemberDesc.TableName;
            string sql = builder.QueryCommandLine;

            DataTable members = DatabaseObject.Instance.ExecuteQuerySQL(sql);

            member.Id               = Convert.ToInt32(members.Rows[0][MemberDesc.Id]);
            member.Name             = Convert.ToString(members.Rows[0][MemberDesc.Name]);
            member.Sex              = Convert.ToInt32(members.Rows[0][MemberDesc.Sex]);
            member.Password         = Convert.ToString(members.Rows[0][MemberDesc.Password]);
            member.DiscountOptionId = Convert.ToInt32(members.Rows[0][MemberDesc.DiscountOptionId]);
            member.Consumption      = Convert.ToInt32(members.Rows[0][MemberDesc.Consumption]);
            member.Id               = Convert.ToInt32(members.Rows[0][MemberDesc.Id]);
            member.Mobile           = Convert.ToString(members.Rows[0][MemberDesc.Mobile]);
            member.Phone            = Convert.ToString(members.Rows[0][MemberDesc.Phone]);
            member.Company          = Convert.ToString(members.Rows[0][MemberDesc.Company]);
            member.Address          = Convert.ToString(members.Rows[0][MemberDesc.Address]);
            member.State            = Convert.ToInt32(members.Rows[0][MemberDesc.State]);
            member.ConsumeCount     = Convert.ToInt32(members.Rows[0][MemberDesc.ConsumeCount]);
            member.RegDate          = Convert.ToDateTime(members.Rows[0][MemberDesc.RegDate]);
            member.LastConsumeDate  = Convert.ToDateTime(members.Rows[0][MemberDesc.LastConsumeDate]);

            return member;
        }

        public static Member GetMember(string name)
        {
            SqlBuilder builder = new SqlBuilder();
            builder.ConditionAdd(MemberDesc.Name, ConditionExpress.EQUALS, name);

            Member[] set = Query(builder.ConditionString);

            if (set != null && set.Length > 0)
            {
                return set[0];
            }

            return null;
        }

        public static Member[] Query(string condition)
        {

            // query factories from database
            string sql = "select * from " + MemberDesc.TableName;
            if (condition != null && condition.Length > 0)
            {
                sql += " where " + condition;
            }
            DataTable table = DatabaseObject.Instance.ExecuteQuerySQL(sql);


            // rebuild the data
            if (table != null && table.Rows.Count > 0)
            {
                Member[] members = new Member[table.Rows.Count];

                for (int i = 0; i < table.Rows.Count; i++)
                {
                    Member member = new Member();

                    member.Id               = Convert.ToInt32(table.Rows[i][MemberDesc.Id]);
                    member.Name             = Convert.ToString(table.Rows[i][MemberDesc.Name]);
                    member.Sex              = Convert.ToInt32(table.Rows[i][MemberDesc.Sex]);
                    member.Password         = Convert.ToString(table.Rows[i][MemberDesc.Password]);
                    member.DiscountOptionId = Convert.ToInt32(table.Rows[i][MemberDesc.DiscountOptionId]);
                    member.Consumption      = Convert.ToInt32(table.Rows[i][MemberDesc.Consumption]);
                    member.Id               = Convert.ToInt32(table.Rows[i][MemberDesc.Id]);
                    member.Mobile           = Convert.ToString(table.Rows[i][MemberDesc.Mobile]);
                    member.Phone            = Convert.ToString(table.Rows[i][MemberDesc.Phone]);
                    member.Company          = Convert.ToString(table.Rows[i][MemberDesc.Company]);
                    member.Address          = Convert.ToString(table.Rows[i][MemberDesc.Address]);
                    member.State            = Convert.ToInt32(table.Rows[i][MemberDesc.State]);
                    member.ConsumeCount     = Convert.ToInt32(table.Rows[i][MemberDesc.ConsumeCount]);
                    member.RegDate          = Convert.ToDateTime(table.Rows[i][MemberDesc.RegDate]);
                    member.LastConsumeDate  = Convert.ToDateTime(table.Rows[i][MemberDesc.LastConsumeDate]);

                    members.SetValue(member, i);
                }

                return members;
            }
            return null;
        }

        public static Int32 Add(Member member)
        {
            SqlBuilder builder = new SqlBuilder();
            builder.InsertAdd(MemberDesc.Name,                  member.Name);
            builder.InsertAdd(MemberDesc.Sex,                   member.Sex);
            builder.InsertAdd(MemberDesc.Password,              member.Password);
            if (member.DiscountOptionId > 0)
            {
               builder.InsertAdd(MemberDesc.DiscountOptionId, member.DiscountOptionId);
            }
            builder.InsertAdd(MemberDesc.Consumption,           member.Consumption);
            builder.InsertAdd(MemberDesc.Mobile,                member.Mobile);
            builder.InsertAdd(MemberDesc.Phone,                 member.Phone);
            builder.InsertAdd(MemberDesc.Company,               member.Company);
            builder.InsertAdd(MemberDesc.Address,               member.Address);
            builder.InsertAdd(MemberDesc.State,                 member.State);
            builder.InsertAdd(MemberDesc.ConsumeCount,          member.ConsumeCount);
            builder.InsertAdd(MemberDesc.RegDate,               member.RegDate);
            builder.InsertAdd(MemberDesc.LastConsumeDate,       member.LastConsumeDate);
            
            builder.QueryTableName = MemberDesc.TableName;

            string sql = builder.InsertCommandLine;

            return DatabaseObject.Instance.ExecuteNonQuerySQL(sql);
        }

        public static bool Delete(Int32 id)
        {
            SqlBuilder builder = new SqlBuilder();
            builder.ConditionAdd(MemberDesc.Id, ConditionExpress.EQUALS, id);
            builder.QueryTableName = MemberDesc.TableName;
            string sql = builder.DeleteCommandLine;

            return (DatabaseObject.Instance.ExecuteNonQuerySQL(sql) != -1);
        }

        public static Int32 Update(Member member)
        {
            SqlBuilder builder = new SqlBuilder();
            builder.UpdateAdd(MemberDesc.Name,                   member.Name);
            builder.UpdateAdd(MemberDesc.Sex,                    member.Sex);
            builder.UpdateAdd(MemberDesc.Password,               member.Password);
            builder.UpdateAdd(MemberDesc.DiscountOptionId,       member.DiscountOptionId);
            builder.UpdateAdd(MemberDesc.Consumption,            member.Consumption);
            builder.UpdateAdd(MemberDesc.Mobile,                 member.Mobile);
            builder.UpdateAdd(MemberDesc.Phone,                  member.Phone);
            builder.UpdateAdd(MemberDesc.Company,                member.Company);
            builder.UpdateAdd(MemberDesc.Address,                member.Address);
            builder.UpdateAdd(MemberDesc.State,                  member.State);
            builder.UpdateAdd(MemberDesc.ConsumeCount,           member.ConsumeCount);
            builder.UpdateAdd(MemberDesc.RegDate,                member.RegDate);
            builder.UpdateAdd(MemberDesc.LastConsumeDate,        member.LastConsumeDate);

            builder.ConditionAdd(MemberDesc.Id, ConditionExpress.EQUALS, member.Id);
            builder.QueryTableName = MemberDesc.TableName;

            string sql = builder.UpdateCommandLine;

            return DatabaseObject.Instance.ExecuteNonQuerySQL(sql);
        }
    }
}
